<?
/**
 * Database.php
 *
 * The Database class is meant to simplify the task of accessing
 * information from the website's database.
 */
include("constants.php");

class MySQLDB
{
	var $connection;         //The MySQL database connection
	var $num_active_users;   //Number of active users viewing site
	var $num_active_guests;  //Number of active guests viewing site
	var $num_members;        //Number of signed-up users
	var $num_resources;      //Number of resources logged
	/* Note: call getNumMembers() to access $num_members! */

	/* Class constructor */
	function MySQLDB()
	{
		/* Make connection to database */
		$this->connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
		mysql_select_db(DB_NAME, $this->connection) or die(mysql_error());

		/**
		* Only query database to find out number of members
		* when getNumMembers() is called for the first time,
		* until then, default value set.
		*/
		$this->num_members = -1;

		if(TRACK_VISITORS)
		{
			/* Calculate number of users at site */
			$this->calcNumActiveUsers();

			/* Calculate number of guests at site */
			$this->calcNumActiveGuests();
		}
	}

	// email verification link
	function verifyUser($username, $id)
	{
		$result = $this->query("SELECT active FROM ".TBL_USERS." WHERE username = '$username' AND userid = '$id'");
		if(mysql_num_rows($result) > 0)
		{
			$this->updateUserField($username, "active", 1);
			return true;
		}
		return false;
	}

	/**
	* confirmUserPass - Checks whether or not the given
	* username is in the database, if so it checks if the
	* given password is the same password in the database
	* for that user. If the user doesn't exist or if the
	* passwords don't match up, it returns an error code
	* (1 or 2). On success it returns 0.
	*/
	function confirmUserPass($username, $password)
	{
		/* Add slashes if necessary (for query) */
		if(!get_magic_quotes_gpc())
		{
			$username = addslashes($username);
		}

		/* Verify that user is in database */
		$q = "SELECT password,active FROM ".TBL_USERS." WHERE username = '$username'";
		$result = mysql_query($q, $this->connection);
		if(!$result || (mysql_num_rows($result) < 1))
		{
			return 1; //Indicates username failure
		}

		/* Retrieve password from result, strip slashes */
		$dbarray = mysql_fetch_array($result);
		$dbarray['password'] = stripslashes($dbarray['password']);
		$password = stripslashes($password);

		/* Validate that password is correct */
		if($password == $dbarray['password'])
		{
			return 0; //Success! Username and password confirmed
		}
		else if($dbarray['active']==0)
		{
			return 3;//Indicates account is not verified
		}
		else
		{
			return 2; //Indicates password failure
		}
	}

	/**
	* confirmUserID - Checks whether or not the given
	* username is in the database, if so it checks if the
	* given userid is the same userid in the database
	* for that user. If the user doesn't exist or if the
	* userids don't match up, it returns an error code
	* (1 or 2). On success it returns 0.
	*/
	function confirmUserID($username, $userid)
	{
		/* Add slashes if necessary (for query) */
		if(!get_magic_quotes_gpc())
		{
			$username = addslashes($username);
		}

		/* Verify that user is in database */
		$q = "SELECT userid FROM ".TBL_USERS." WHERE username = '$username'";
		$result = mysql_query($q, $this->connection);
		if(!$result || (mysql_num_rows($result) < 1))
		{
			return 1; //Indicates username failure
		}

		/* Retrieve userid from result, strip slashes */
		$dbarray = mysql_fetch_array($result);
		$dbarray['userid'] = stripslashes($dbarray['userid']);
		$userid = stripslashes($userid);

		/* Validate that userid is correct */
		if($userid == $dbarray['userid'])
		{
			return 0; //Success! Username and userid confirmed
		}
		else
		{
			return 2; //Indicates userid invalid
		}
	}

	/**
	* usernameTaken - Returns true if the username has
	* been taken by another user, false otherwise.
	*/
	function usernameTaken($username)
	{
		if(!get_magic_quotes_gpc())
		{
			$username = addslashes($username);
		}
		$q = "SELECT username FROM ".TBL_USERS." WHERE username = '$username'";
		$result = mysql_query($q, $this->connection);
		return (mysql_num_rows($result) > 0);
	}

	/**
	* emailTaken - Returns true if the email address has
	* been taken by another user, false otherwise.
	*/
	function emailTaken($email)
	{
		if(!get_magic_quotes_gpc())
		{
			$email = addslashes($email);
		}
		$q = "SELECT email FROM ".TBL_USERS." WHERE email = '$email'";
		$result = mysql_query($q, $this->connection);
		return (mysql_num_rows($result) > 0);
	}

	/**
	* usernameBanned - Returns true if the username has
	* been banned by the administrator.
	*/
	function usernameBanned($username)
	{
		if(!get_magic_quotes_gpc())
		{
			$username = addslashes($username);
		}
		$q = "SELECT username FROM ".TBL_BANNED_USERS." WHERE username = '$username'";
		$result = mysql_query($q, $this->connection);
		return (mysql_num_rows($result) > 0);
	}

	/**
	* addNewUser - Inserts the given (username, password, email)
	* info into the database. Appropriate user level is set.
	* Returns true on success, false otherwise.
	*/
	function addNewUser($username, $password, $email, $country, $gender, $randomID)
	{
		$time = time();
		/* If admin sign up, give admin user level */
		if(strcasecmp($username, ADMIN_NAME) == 0)
		{
			$ulevel = ADMIN_LEVEL;
		}
		else
		{
			$ulevel = USER_LEVEL;
		}
		$q = "INSERT INTO ".TBL_USERS." (userid, username, password, userlevel, email, timestamp, active, regDate, country, gender) VALUES ('$randomID', '$username', '$password', $ulevel, '$email', $time, 0, $time, '$country', '$gender')";
		return mysql_query($q, $this->connection);
	}

	/**
	* updateUserField - Updates a field, specified by the field
	* parameter, in the user's row of the database.
	*/
	function updateUserField($username, $field, $value)
	{
		$q = "UPDATE ".TBL_USERS." SET ".$field." = '$value' WHERE username = '$username'";
		return mysql_query($q, $this->connection);
	}

	//sets the project's picture location
	function addProfilePic($user, $location)
	{
		$result = $this->query("SELECT profilePic FROM ".TBL_USERS." WHERE username='$user'");
		$dbarray = mysql_fetch_array($result);
		if($dbarray['profilePic']!='')
		{
			$this->deletePhoto($dbarray['profilePic']);
		}
		return $this->query("UPDATE ".TBL_USERS." SET profilePic='$location' WHERE username='$user'");
	}

	//sets the project's picture location
	function addProjectPic($id, $location)
	{
		$result = $this->query("SELECT logoPic FROM ".TBL_PROJECTS." WHERE id='$id'");
		$dbarray = mysql_fetch_array($result);
		if($dbarray['logoPic']!='')
		{
			$this->deletePhoto($dbarray['logoPic']);
		}
		return $this->query("UPDATE ".TBL_PROJECTS." SET logoPic='$location' WHERE id='$id'");
	}
	function deleteProjectPic($id)
	{
		return $this->query("UPDATE ".TBL_PROJECTS." SET logoPic='' WHERE id='$id'");
	}
	function deletePhoto($location)
	{
		$con = ftp_connect(FTP_SERVER) or die("Could not connect");
		if(ftp_login($con, FTP_USER, FTP_PASS))
		{
			if(ftp_delete($con, SITE_DIRECTORY.$location))
			{
				return true;
			}
		}
		return false;
	}

	/**
	* getUserInfo - Returns the result array from a mysql
	* query asking for all information stored regarding
	* the given username. If query fails, NULL is returned.
	*/
	function getUserInfo($username)
	{
		$q = "SELECT * FROM ".TBL_USERS." WHERE username = '$username'";
		$result = mysql_query($q, $this->connection);
		/* Error occurred, return given name by default */
		if(!$result || (mysql_num_rows($result) < 1))
		{
			return NULL;
		}
		/* Return result array */
		$dbarray = mysql_fetch_array($result);
		return $dbarray;
	}

	/**
	* getNumMembers - Returns the number of signed-up users
	* of the website, banned members not included. The first
	* time the function is called on page load, the database
	* is queried, on subsequent calls, the stored result
	* is returned. This is to improve efficiency, effectively
	* not querying the database when no call is made.
	*/
	function getNumMembers()
	{
		if($this->num_members < 0)
		{
			$q = "SELECT timestamp FROM ".TBL_USERS;
			$result = mysql_query($q, $this->connection);
			$this->num_members = mysql_numrows($result);
		}
		return $this->num_members;
	}

	// get locations for all members to show on google maps
	function getAllLocationsWithNames()
	{
		$result = $this->query("SELECT latitude,longitude,username FROM ".TBL_USERS);
		while($user = mysql_fetch_assoc($result))
		{
			if ($user["latitude"]) {
				$ret .= "[".$user["latitude"].",".$user["longitude"].",'".$user["username"]."'],";
			}
		}
		$ret = substr($ret, 0, -1);
		return $ret;
	}
	// get locations for all project members to show on google maps
	function getAllMemberLocationsWithNames($projectID)
	{
		$result = $this->query("SELECT username FROM ".TBL_PROJECT_MEMBERS." WHERE projectID='$projectID' AND userlevel>=1");
		$num_rows = mysql_num_rows($result);
		for($i=0;$i<$num_rows;$i++)
		{
			$name = mysql_result($result,$i,"username");
			$retval = $this->query("SELECT latitude,longitude,username FROM ".TBL_USERS." WHERE username='$name'");
			$user = mysql_fetch_array($retval);
			if ($user["latitude"])
			{
				$ret .= "[".$user["latitude"].",".$user["longitude"].",'".$user["username"]."'],";
			}
		}
		$ret = substr($ret, 0, -1);
		return $ret;
	}

	//resources count
	function getNumResources()
	{
		$q = "SELECT timestamp FROM ".TBL_RESOURCES;
		$result = mysql_query($q, $this->connection);
		return mysql_numrows($result);
	}
	//projects count
	function getNumProjects()
	{
		$q = "SELECT timestamp FROM ".TBL_PROJECTS;
		$result = mysql_query($q, $this->connection);
		return mysql_numrows($result);
	}
	function getProject($id)
	{
		$q = "SELECT * FROM ".TBL_PROJECTS." WHERE id='$id'";
		$result = mysql_query($q, $this->connection);
		/* Error occurred, return given name by default */
		if(!$result || (mysql_num_rows($result) < 1))
		{
			return NULL;
		}
		/* Return result array */
		$dbarray = mysql_fetch_array($result);
		return $dbarray;
	}

	//counts comments
	function getCommentCount($attachid)
	{
		$q = "SELECT * FROM ".TBL_COMMENTS." WHERE attachid='$attachid'";
		$result = mysql_query($q, $this->connection);
		return mysql_numrows($result);
	}
	//delte comment by id
	function deleteComment($id)
	{
		$q = "DELETE FROM ".TBL_COMMENTS." WHERE id='$id'";
		mysql_query($q, $this->connection);
	}

	//delete resource by id and username
	function deleteResource($id,$username)
	{
		global $session;
		$q = "DELETE FROM ".TBL_RESOURCES." WHERE id='$id' AND username='$username'";
		return mysql_query($q, $this->connection);
	}

	//delte needed resource by id and username
	function deleteNeededResource($id,$projectID)
	{
		global $session;
		$q = "DELETE FROM ".TBL_NEEDS." WHERE id='$id' AND projectid='$projectID'";
		return mysql_query($q, $this->connection);
	}

	//delte category by id
	function deleteCategory($id)
	{
		global $session;
		$q = "DELETE FROM ".TBL_CATEGORIES." WHERE id='$id'";
		return mysql_query($q, $this->connection);
	}

	//delte message by id and username
	function deleteMessage($id,$username)
	{
		global $session;
		$q = "DELETE FROM ".TBL_MESSAGES." WHERE id='$id' AND toUser='$username'";
		return mysql_query($q, $this->connection);
	}
	//mark message read
	function markRead($id)
	{
		global $session;
		$this->query("UPDATE ".TBL_MESSAGES." SET viewed='1' WHERE id='$id' AND toUser='".$session->username."'");
	}
	
	//addFeedback - Inserts the given feedback info into the database.
	function addFeedback($id, $name, $email, $subject, $feedback)
	{
		$time = time();
		$q = "INSERT INTO ".TBL_FEEDBACK." (id, username, email, subject, feedback, timestamp) VALUES ('$id', '$name', '$email', '$subject', '$feedback', $time)";
		return mysql_query($q, $this->connection);
	}
	//deleteFeedback - Delete feedback with the specified id
	function deleteFeedback($id)
	{
		$q = "DELETE FROM ".TBL_FEEDBACK." WHERE id = '$id'";
		return mysql_query($q, $this->connection);
	}
	//deleteLink - Delete link with the specified id
	function deleteLink($id)
	{
		global $session;
		
		if($session->userlevel>=MANAGER_LEVEL)
		{
			$this->query("DELETE FROM ".TBL_LINKS." WHERE id='$id'");
			$this->query("DELETE FROM ".TBL_LIKES." WHERE likeID='$id' AND type=1");
		}
		else
		{
			if($this->query("DELETE FROM ".TBL_LINKS." WHERE id='$id' AND creator='".$session->username."'"))
			{
				$this->query("DELETE FROM ".TBL_LIKES." WHERE likeID='$id' AND type=1");
			}
		}
		return true;
	}

	//delte user by username from all tables
	function deleteUser($user)
	{
		global $session;
		$result = $this->query("SELECT profilePic FROM ".TBL_USERS." WHERE username='$user'");
		if($session->isAdmin())
		{
			$arrayData = mysql_fetch_array($result);
			if($arrayData['profilePic']!="")
			{
				$this->deletePhoto($arrayData['profilePic']);
			}
			$this->query("DELETE FROM ".TBL_MESSAGES." WHERE toUser='$user' OR fromUser='$user'");
			$this->query("DELETE FROM ".TBL_RESOURCES." WHERE username='$user'");
			$this->query("DELETE FROM ".TBL_USERS." WHERE username='$user'");
			$this->query("DELETE FROM ".TBL_ACTIVE_USERS." WHERE username='$user'");
			return true;
		}
		return false;
	}

	//delte project by id
	function deleteProject($id)
	{
		$result = $this->query("SELECT logoPic FROM ".TBL_PROJECTS." WHERE id='$id'");
		if(mysql_num_rows($result)>0)
		{
			$arrayData = mysql_fetch_array($result);
			if($arrayData['logoPic']!="")
			{
				$this->deletePhoto($arrayData['logoPic']);
			}
			$this->query("DELETE FROM ".TBL_NEEDS." WHERE projectid='$id'");
			$this->query("DELETE FROM ".TBL_PROJECTS." WHERE id='$id'");
			$this->query("DELETE FROM ".TBL_PROJECT_MEMBERS." WHERE projectID='$id'");
			$this->query("DELETE FROM ".TBL_PROJECT_NEWS." WHERE projectID='$id'");
			$this->query("DELETE FROM ".TBL_LIKES." WHERE likeID='$id' AND type=0");
			return true;
		}
		else
			return false;
	}
	function addLike($likeID, $type)
	{
		global $session;
		
		$result = $this->query("SELECT * FROM ".TBL_LIKES." WHERE likeID='$likeID' AND type=$type AND username='".$session->username."'");
		$num_rows = mysql_num_rows($result);
		if($num_rows>0)
		{
			return false;//failed, already in database
		}
		else
		{
			$randID = $session->generateRandID();
			$time = $session->time;
			$this->query("INSERT INTO ".TBL_LIKES." (id, likeID, type, username, timestamp) VALUES ('$randID', '$likeID', $type, '".$session->username."', $time)");
			if($type==0)//project
			{
				$this->query("UPDATE ".TBL_PROJECTS." SET likes=likes+1 WHERE id='$likeID'");
			}
			else if($type==1)//link
			{
				$this->query("UPDATE ".TBL_LINKS." SET likes=likes+1 WHERE id='$likeID'");
			}
			return true;//successful
		}
	}

	/**
	* calcNumActiveUsers - Finds out how many active users
	* are viewing site and sets class variable accordingly.
	*/
	function calcNumActiveUsers()
	{
		/* Calculate number of users at site */
		$q = "SELECT * FROM ".TBL_ACTIVE_USERS;
		$result = mysql_query($q, $this->connection);
		$this->num_active_users = mysql_numrows($result);
	}

	/**
	* calcNumActiveGuests - Finds out how many active guests
	* are viewing site and sets class variable accordingly.
	*/
	function calcNumActiveGuests()
	{
		/* Calculate number of guests at site */
		$q = "SELECT * FROM ".TBL_ACTIVE_GUESTS;
		$result = mysql_query($q, $this->connection);
		$this->num_active_guests = mysql_numrows($result);
	}

	/**
	* addActiveUser - Updates username's last active timestamp
	* in the database, and also adds him to the table of
	* active users, or updates timestamp if already there.
	*/
	function addActiveUser($username, $time)
	{
		$q = "UPDATE ".TBL_USERS." SET timestamp = '$time' WHERE username = '$username'";
		mysql_query($q, $this->connection);

		if(!TRACK_VISITORS)
			return;

		$q = "REPLACE INTO ".TBL_ACTIVE_USERS." VALUES ('$username', '$time')";
		mysql_query($q, $this->connection);
		$this->calcNumActiveUsers();
	}

	/* addActiveGuest - Adds guest to active guests table */
	function addActiveGuest($ip, $time)
	{
		if(!TRACK_VISITORS)
			return;

		$q = "REPLACE INTO ".TBL_ACTIVE_GUESTS." VALUES ('$ip', '$time')";
		mysql_query($q, $this->connection);
		$this->calcNumActiveGuests();
	}

	/* These functions are self explanatory, no need for comments */

	/* removeActiveUser */
	function removeActiveUser($username)
	{
		if(!TRACK_VISITORS) return;
		$q = "DELETE FROM ".TBL_ACTIVE_USERS." WHERE username = '$username'";
		mysql_query($q, $this->connection);
		$this->calcNumActiveUsers();
	}

	/* removeActiveGuest */
	function removeActiveGuest($ip)
	{
		if(!TRACK_VISITORS)
			return;

		$q = "DELETE FROM ".TBL_ACTIVE_GUESTS." WHERE ip = '$ip'";
		mysql_query($q, $this->connection);
		$this->calcNumActiveGuests();
	}

	/* removeInactiveUsers */
	function removeInactiveUsers()
	{
		if(!TRACK_VISITORS)
			return;

		$timeout = time()-USER_TIMEOUT*60;
		$q = "DELETE FROM ".TBL_ACTIVE_USERS." WHERE timestamp < $timeout";
		mysql_query($q, $this->connection);
		$this->calcNumActiveUsers();
	}

	/* removeInactiveGuests */
	function removeInactiveGuests()
	{
		if(!TRACK_VISITORS)
			return;

		$timeout = time()-GUEST_TIMEOUT*60;
		$q = "DELETE FROM ".TBL_ACTIVE_GUESTS." WHERE timestamp < $timeout";
		mysql_query($q, $this->connection);
		$this->calcNumActiveGuests();
	}

	/**
	* query - Performs the given query on the database and
	* returns the result, which may be false, true or a
	* resource identifier.
	*/
	function query($query)
	{
		return mysql_query($query, $this->connection);
	}
};

/* Create database connection */
$database = new MySQLDB;
?>